package com.smartcloud.scadmintable.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.smartcloud.common.util.Guid;
import com.smartcloud.scadmintable.dao.SysTableColumnMapper;
import com.smartcloud.scadmintable.dao.SysTableMapper;
import com.smartcloud.scadmintable.model.SysTable;
import com.smartcloud.scadmintable.model.SysTableColumn;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;

@Service
public class SysTableService {
	
	@Autowired
	private SysTableMapper sysTableMapper;
	
	@Autowired
	private SysTableColumnMapper sysTableColumnMapper;
	
	@Autowired
	private DataSource dataSource;
	
	@Transactional
	public int insert(SysTable sysTable) throws SQLException{
		List<SysTableColumn> columnList = sysTable.getSysTableColumnList();
		for(SysTableColumn col : columnList){
			col.setId(Guid.newGuid());
			col.setTableId(sysTable.getId());
			sysTableColumnMapper.insert(col);
		}
		createTable(sysTable);
		return sysTableMapper.insert(sysTable);
	}
	
	

	public Page<SysTable> selectAll(SysTable sysTable, int pageNo, int pageSize){
		PageHelper.startPage(pageNo, pageSize);
		return (Page<SysTable>)sysTableMapper.selectAll();
	}
	
	@Transactional
	public int updateByPrimaryKeySelective(SysTable sysTable) throws SQLException{
		List<SysTableColumn> columnList = sysTable.getSysTableColumnList();
		//先将子表数据都删除
		SysTableColumn record = new SysTableColumn();
		record.setTableId(sysTable.getId());
		sysTableColumnMapper.delete(record);
		
		//重新插入
		for(SysTableColumn col : columnList){
			col.setId(Guid.newGuid());
			col.setTableId(sysTable.getId());
			sysTableColumnMapper.insert(col);
		}
		dropTable(sysTable);
		createTable(sysTable);
		
		return sysTableMapper.updateByPrimaryKeySelective(sysTable);
	}
	
	@Transactional
	public int deleteByPrimaryKey(String id) throws SQLException{
		//先删除子表数据
		try {
			SysTable st = sysTableMapper.selectByPrimaryKey(id);
			dropTable(st);
		}finally {
			SysTableColumn record = new SysTableColumn();
			record.setTableId(id);
			sysTableColumnMapper.delete(record);
			return sysTableMapper.deleteByPrimaryKey(id);
		}
	}
	
	@Transactional
	public int deleteByPrimaryKeys(String[] ids) throws SQLException{ 
		for(String id: ids){
			//先删除子表数据
			SysTableColumn record = new SysTableColumn();
			record.setTableId(id);
			sysTableColumnMapper.delete(record);
			
			SysTable st = sysTableMapper.selectByPrimaryKey(id);
			dropTable(st);
		}
		
		return sysTableMapper.deleteByPrimaryKeys(ids);
	}
	
	public Page<SysTable> selectAllWithList(SysTable sysTable, int pageNo, int pageSize){
		PageHelper.startPage(pageNo, pageSize);
		return (Page<SysTable>)sysTableMapper.selectAllWithList(sysTable);
	}
	
	/***
	 * 创建表
	 * @param sysTable
	 * @throws SQLException 
	 */
	private void createTable(SysTable sysTable) throws SQLException {
		List<SysTableColumn> columnList = sysTable.getSysTableColumnList();
		//创建表 
		StringBuffer sql = new StringBuffer();
		sql.append(" CREATE TABLE IF NOT EXISTS `" + sysTable.getName() + "` ( ");
		for(SysTableColumn stc: columnList){
			String colStr = "`" + stc.getName() + "` " + stc.getColumnType();
			
			//数据类型
			if(stc.getColumnType().equals("varchar")){
				colStr += "(" + stc.getColumnLength() + ") ";
			}else if(stc.getColumnType().equals("Decimal")){
				colStr +=  "(" + stc.getColumnLength() + ", " + stc.getColumnDecimal() + ") ";
			}
			//是否为空
			if(stc.getIsAllowNull().equals("false")){
				colStr += " NOT NULL ";
			}
			//默认值
			if(stc.getDefaultValue() != null && !stc.getDefaultValue().equals("")){
				colStr += " DEFAULT " + stc.getDefaultValue();
			}
			colStr += " COMMENT \'" + stc.getDescription() + "\', ";
			sql.append(colStr);
		}
		sql.append(" PRIMARY KEY (`id`))");
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.execute();
		
	}
	
	private void dropTable(SysTable sysTable) throws SQLException{
		String sql = "drop table `" + sysTable.getName() + "`;";
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		ps.execute();
	}
}
